# -*- Coding = utf-8 -*-
# @time: 2021/2/1 9:43
# Author: YKL

"""
通讯录
"""

import MySQLdb

conn = None

def ensure_connection():
    global conn
    if not conn:
        conn = MySQLdb.connect(host='39.105.56.50',port=3306,
                       user='hellokitty',password='Hellokitty.618',
                       database='python2005',charset='utf8')
    return conn


def add_contact():
    ensure_connection()
    name = input('姓名:')
    tel = input('电话:')
    email = input('邮箱:')
    company = input('公司:')
    memo = input('备注:')
    try:
        with conn.cursor() as cursor:
            affected_rows = cursor.execute(
                'insert into tb_contacts (name, tel, email, company, memo) '
                'values (%s,%s,%s,%s,%s)',
                (name, tel, email, company, memo)
            )
            if affected_rows == 1:
                print('新建联系人成功!')
        conn.commit()
    except MySQLdb.MySQLError as err:
        print(err)
        conn.rollback()


def delete_contact(contact_id):
    ensure_connection()
    try:
        with conn.cursor() as cursor:
            affected_rows = cursor.execute('delete from tb_contacts where id=%s',(contact_id,))
            if affected_rows == 1:
                print('已删除')
            conn.commit()
    except MySQLdb.MySQLError as err:
        print(err)
        conn.rollback()


def edit_contact(contact_id, contact_dict):
    ensure_connection()
    name = input('姓名:').strip() or contact_dict['name']
    tel = input('电话:').strip() or contact_dict['tel']
    email = input('邮箱:').strip() or contact_dict['email']
    company = input('公司:').strip() or contact_dict['company']
    memo = input('备注:').strip() or contact_dict['memo']
    try:
        with conn.cursor() as cursor:
            affected_rows = cursor.execute(
                'update tb_contacts set name=%s, tel=%s, email=%s, company=%s, memo=%s '
                'where id=%s',
                (name, tel, email, company, memo, contact_id)
            )
            if affected_rows == 1:
                print('联系人已修改')
        conn.commit()
    except MySQLdb.MySQLError as err:
        print(err)
        conn.rollback()


def show_contact_detail(contact_id):
    ensure_connection()
    with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
        cursor.execute(
            'select name, tel, email, company, memo from tb_contacts where id=%s',
            (contact_id,)
        )
        contact_dict = cursor.fetchone()
        print(f'\n姓名:{contact_dict["name"]}')
        print(f'电话:{contact_dict["tel"]}')
        print(f'邮箱:{contact_dict["email"]}')
        print(f'公司:{contact_dict["company"]}')
        print(f'备注:{contact_dict["memo"]}\n')
        print('1.删除联系人')
        print('2.编辑联系人')
        try:
            choice = int(input('请选择:'))
            if choice ==1:
                confirm = input(f'确定删除联系人{contact_dict["name"]}的信息?(yes/no)')
                if confirm.strip().lower() == 'yes':
                    delete_contact(contact_id)
            elif choice == 2:
                edit_contact(contact_id, contact_dict)
        except ValueError:
            pass

def show_all_contacts():
    ensure_connection()
    with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
        cursor.execute('select id, name from tb_contacts')
        all_rows = cursor.fetchall()
        for index, row_dict in enumerate(all_rows):
            print(f'[{index + 1}]: {row_dict["name"]}')
        try:
            choice = int(input('输入编号查看联系人详情:'))
            if 0 < choice <= len(all_rows):
                contact_id = all_rows[choice - 1]['id']
                show_contact_detail(contact_id)
        except ValueError:
            pass


def search_contacts():
    ensure_connection()
    keyword = input('关键字:')
    keyword = f'%{keyword}%'
    with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
        cursor.execute(
            'select id, name from tb_contacts '
            ' where name like %s or company like %s or memo like %s',
            (keyword, keyword, keyword)
        )
        all_rows = cursor.fetchall()
        for index, row_dict in enumerate(all_rows):
            print(f'[{index + 1}]: {row_dict["name"]}')
        try:
            choice = int(input('输入编号查看联系人详情:'))
            if 0< choice <= len(all_rows):
                contact_id = all_rows[choice -1]['id']
                show_contact_detail(contact_id)
        except ValueError:
            pass


def list_contacts():
    ensure_connection()
    print('1.查看所有联系人')
    print('2.搜索联系人')
    print('3.返回')
    choice = 0
    while choice <= 0 or choice > 3:
        try:
            choice = int(input('请选择:'))
        except ValueError:
            pass
    if choice == 1:
        show_all_contacts()
    elif choice == 2:
        search_contacts()

def main():
    ensure_connection()
    print('欢迎使用通讯录程序'.center(20,'='))
    while True:
        print('1.新增联系人')
        print('2.查看联系人')
        print('3.退出程序')
        choice = 0
        while choice <=0 or choice > 3:
            try:
                choice = int(input('请选择:'))
            except ValueError:
                pass
        if choice == 1:
            add_contact()
        elif choice == 2:
            list_contacts()
        else:
            print('程序结束'.center(20,'='))
            break

if __name__ == '__main__':
    main()